﻿using System.Configuration.Provider;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.ComponentModel.DataAnnotations;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;

namespace MvcApplication1.Models
{
    public class Manage
    {
        private MembershipBrand _brand;
        private string connectionString;
        private DataTable itemlist=new DataTable();
        private DataTable itemKeyword = new DataTable();
        //private DataTable brandinfo=new DataTable();
        public DataView ItemList
        { 
            get{ return itemlist.DefaultView;} 
        }
        //public DataView BrandInfo { get { return brandinfo.DefaultView; } }
        public MembershipBrand brand { get { return _brand; } }

        //------------------for unit test-----------------------------------------------------
        public Manage()   
        {
            //_brand = new MembershipBrand("匹克", "df", "", 2,new DateTime(2001,2,3), "", 0);
            //connectionString = @"Data Source=ADF4430A0F96498\SQL2008;Initial Catalog=cj;Integrated Security=True";
            //InitItemList();
        }
        //------------------for unit test--------------------------------------------------

        public Manage(MembershipBrand brand) 
        {
            _brand = brand;
            ConnectionStringSettings ConnectionStringSettings =
                  ConfigurationManager.ConnectionStrings["cj"];

            if (ConnectionStringSettings == null || ConnectionStringSettings.ConnectionString.Trim() == "")
            {
                throw new ProviderException("Connection string cannot be blank.");
            }
            connectionString = ConnectionStringSettings.ConnectionString;
            InitItemList();
            //InitBrandInfo();
        }


        private void InitItemList()
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = new SqlCommand("select i.[ID],[itemName],[itemSN],[image],[addTime],i.[linkAddr],[price],[intro] from [item] as i inner join [brand] as b on i.brandID=b.ID where brandname=@brandname", conn);
            da.SelectCommand.Parameters.Add("@brandname", SqlDbType.NVarChar).Value = _brand.brandName;
            try
            {
                conn.Open();
                da.Fill(itemlist);
                DataColumn[] key = new DataColumn[1];
                key[0] = itemlist.Columns["ID"];
                itemlist.PrimaryKey = key;
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        //private void InitBrandInfo()
        //{
        //    SqlConnection conn = new SqlConnection(connectionString);
        //    SqlDataAdapter da = new SqlDataAdapter();
        //    da.SelectCommand = new SqlCommand("select b.[brandIntro],b.[registerTime],b.[linkAddr],b.[logo],b.[itemCount] from [brand] as b inner join [user] as u on b.userID=u.ID where b.brandname=@brandname", conn);
        //    da.SelectCommand.Parameters.Add("@brandname", SqlDbType.NVarChar).Value = _brand.brandName;
        //    try
        //    {
        //        conn.Open();
        //        da.Fill(brandinfo);
        //    }
        //    catch (SqlException e)
        //    {
        //        throw e;
        //    }
        //    finally
        //    {
        //        conn.Close();
        //    }
        //}
        //get keyword for one item
        public DataView GetItemKeyword(Int64 itemID)
        {
            if (itemKeyword != null) itemKeyword.Clear();   //clear the data stored last time
            SqlConnection conn = new SqlConnection(connectionString);
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = new SqlCommand("select [ID],[keywordSN],[keyword],[itemID] from [itemKeyword] where itemID=@itemID order by [keywordSN]", conn);
            da.SelectCommand.Parameters.Add("@itemID", SqlDbType.BigInt).Value = itemID;
            try
            {
                conn.Open();
                da.Fill(itemKeyword);
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return itemKeyword.DefaultView;
        }
        public void SaveItem(ItemEdit item)
        {
            //---------------------------------------------------update the datatable-----------------------------------------------
            object[] editrow = new object[8];
            editrow[0] = item.GetItemID();
            editrow[1] = item.GetItemName();
            editrow[2] = item.GetItemSN();
            editrow[3] = item.GetImage();
            editrow[4] = item.GetAddTime();
            editrow[5] = item.GetLinkAddr();
            editrow[6] = item.GetPrice();
            editrow[7] = item.GetIntro();
            itemlist.BeginLoadData();
            itemlist.LoadDataRow(editrow, true);
            itemlist.EndLoadData();
            //------------------------------------------------------update the database----------------------------------------------------
            SqlConnection conn = new SqlConnection(connectionString);
            //SqlConnection conn = new SqlConnection(@"Data Source=ADF4430A0F96498\SQL2008;Initial Catalog=cj;Integrated Security=True"); //单元测试
            SqlCommand cmd = new SqlCommand("UPDATE [item] SET [itemname]=@itemname,[itemSN]=@itemSN,[image]=@image,[addTime]=@addtime,[linkAddr]=@linkaddr,[price]=@price,[intro]=@intro WHERE [ID]=@id", conn);
            cmd.Parameters.Add("@itemname", SqlDbType.NVarChar).Value = item.GetItemName();
            cmd.Parameters.Add("@itemSN", SqlDbType.BigInt).Value = item.GetItemSN();
            if (string.IsNullOrEmpty(item.GetImage()))
                cmd.Parameters.Add("@image", SqlDbType.NVarChar).Value = DBNull.Value;
            else
                cmd.Parameters.Add("@image", SqlDbType.NVarChar).Value = item.GetImage();
            cmd.Parameters.Add("@addtime", SqlDbType.DateTime).Value = item.GetAddTime();
            cmd.Parameters.Add("@linkaddr", SqlDbType.NVarChar).Value = item.GetLinkAddr();
            cmd.Parameters.Add("@price", SqlDbType.Money).Value = item.GetPrice();
            if(string.IsNullOrEmpty(item.GetIntro()))
                cmd.Parameters.Add("@intro", SqlDbType.NVarChar).Value = DBNull.Value;
            else
                cmd.Parameters.Add("@intro", SqlDbType.NVarChar).Value = item.GetIntro();
            cmd.Parameters.Add("@id", SqlDbType.BigInt).Value = item.GetItemID();
            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }
        public void SaveKeyword(ItemEdit item)
        {
            itemKeyword.AcceptChanges();
            foreach (DataRow row in itemKeyword.Rows)
            {
                row.Delete();
            }
            KeywordItem[] keywordlist = item.GetKeywordList();
            DataView dv = new DataView(itemKeyword, "", "ID", DataViewRowState.Deleted);
            if (keywordlist != null)
            {
                for (int i = 0; i < keywordlist.Length; i++)
                {
                    if (keywordlist[i].KeywordID == 0)    //new keyword
                    {
                        itemKeyword.Rows.Add(new object[4] { 0, i + 1, keywordlist[i].Keyword, item.GetItemID() });
                        continue;
                    }
                    DataRow row = dv[dv.Find(keywordlist[i].KeywordID)].Row;
                    row.RejectChanges();
                    row[1] = i + 1;   //keywordSN
                    row[2] = keywordlist[i].Keyword;
                }
            }
            SqlConnection conn = new SqlConnection(connectionString);
            SqlDataAdapter da = new SqlDataAdapter("select [ID],[keywordSN],[keyword],[itemID] from [itemKeyword] where itemID=@itemID", conn);
            SqlCommand cmd = new SqlCommand("update [itemKeyword] set [keywordSN]=@keywordSN,[keyword]=@keyword,[itemID]=@itemID where [ID]=@id", conn);
            cmd.Parameters.Add("@keywordSN", SqlDbType.TinyInt, 1, "keywordSN");
            cmd.Parameters.Add("@keyword", SqlDbType.NVarChar, 50, "keyword");
            cmd.Parameters.Add("@itemID", SqlDbType.BigInt, 8, "itemID");
            cmd.Parameters.Add("@id", SqlDbType.BigInt, 8, "ID");
            da.UpdateCommand = cmd;

            cmd = new SqlCommand("delete from [itemKeyword] where ID=@id", conn);
            cmd.Parameters.Add("@id", SqlDbType.BigInt, 8, "ID");
            da.DeleteCommand = cmd;

            cmd = new SqlCommand("insert into [itemKeyword] ([itemID],[keywordSN],[keyword]) values (@itemID,@keywordSN,@keyword)", conn);
            cmd.Parameters.Add("@itemID", SqlDbType.BigInt, 8, "itemID");
            cmd.Parameters.Add("@keywordSN", SqlDbType.TinyInt, 1, "keywordSN");
            cmd.Parameters.Add("@keyword", SqlDbType.NVarChar, 50, "keyword");
            da.InsertCommand = cmd;

            da.Update(itemKeyword.Select(null,null,DataViewRowState.Deleted));
            da.Update(itemKeyword.Select(null,null,DataViewRowState.ModifiedCurrent));
            da.Update(itemKeyword.Select(null,null,DataViewRowState.Added));
        }

        public void AddItem(ItemEdit item)
        {
            //-----------------------------------------add to database---------------------------------------
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("AddItem", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@brandname", SqlDbType.NVarChar).Value = _brand.brandName;
            cmd.Parameters.Add("@itemname", SqlDbType.NVarChar).Value = item.GetItemName();
            cmd.Parameters.Add("@itemSN", SqlDbType.BigInt).Value = item.GetItemSN();
            if (string.IsNullOrEmpty(item.GetImage()))
                cmd.Parameters.Add("@image", SqlDbType.NVarChar).Value = DBNull.Value;
            else
                cmd.Parameters.Add("@image", SqlDbType.NVarChar).Value = item.GetImage();
            DateTime addtime = DateTime.Now;
            cmd.Parameters.Add("@addtime", SqlDbType.DateTime).Value = addtime;
            cmd.Parameters.Add("@linkaddr", SqlDbType.NVarChar).Value = item.GetLinkAddr();
            cmd.Parameters.Add("@price", SqlDbType.Money).Value = item.GetPrice();
            if (string.IsNullOrEmpty(item.GetIntro()))
                cmd.Parameters.Add("@intro", SqlDbType.NVarChar).Value = DBNull.Value;
            else
                cmd.Parameters.Add("@intro", SqlDbType.NVarChar).Value = item.GetIntro();
            cmd.Parameters.Add("@itemID", SqlDbType.BigInt).Direction = ParameterDirection.Output;
            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                item.itemid = Convert.ToString(cmd.Parameters["@itemID"].Value);
                item.addtime = Convert.ToString(addtime);
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            //----------------------------------------------------add to datatable-------------------------------------------------------
            itemlist.Rows.Add(new object[] {item.GetItemID(),item.GetItemName(),item.GetItemSN(),item.GetImage(),item.GetAddTime(),item.GetLinkAddr(),item.GetPrice(),item.GetIntro() });
            itemlist.AcceptChanges();
        }
        public void AddKeyword(ItemEdit item)
        {
            //itemKeyword.AcceptChanges();
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("AddKeyword", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@itemID", SqlDbType.BigInt);
            cmd.Parameters.Add("@keyword", SqlDbType.NVarChar);
            KeywordItem[] keywordlist = item.GetKeywordList();
            if (keywordlist != null)
            {
                try
                {
                    conn.Open();
                    for (int i = 0; i < keywordlist.Length; i++)
                    {
                        if (keywordlist[i].KeywordID != -1)
                        {
                            //itemKeyword.Rows.Add(new object[4] { 0, 0, keywordlist[i].Keyword, item.GetItemID() });
                            cmd.Parameters["@itemID"].Value = item.GetItemID();
                            cmd.Parameters["@keyword"].Value = keywordlist[i].Keyword;
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
                catch (SqlException e)
                {
                    throw e;
                }
                finally
                {
                    conn.Close();
                }
            }
            //SqlDataAdapter da = new SqlDataAdapter("select [ID],[keywordSN],[keyword],[itemID] from [itemKeyword] where itemID=@itemID", conn);
            //cmd.Parameters.Add("@itemID", SqlDbType.BigInt, 8, "itemID");
            //cmd.Parameters.Add("@keyword", SqlDbType.NVarChar, 50, "keyword");
            //cmd.Parameters.Add("@keywordSN", SqlDbType.TinyInt,1,"keywordSN").Direction = ParameterDirection.Output;
            //cmd.UpdatedRowSource = UpdateRowSource.OutputParameters;
            //da.InsertCommand = cmd;
            //da.Update(itemKeyword);
        }

        public void DelItem(Int64[] itemids)
        {
            List <SqlDataRecord> itemid_list = new List<SqlDataRecord>();
            SqlMetaData[] tvp_definition = {new SqlMetaData("itemid", SqlDbType.BigInt)};

            foreach (int itemid in itemids) {
                SqlDataRecord rec = new SqlDataRecord(tvp_definition);
                rec.SetInt64(0, itemid);
                itemid_list.Add(rec);
            }
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("DelItemAndKeyword", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@itemidlist", SqlDbType.Structured);
            cmd.Parameters["@itemidlist"].Direction = ParameterDirection.Input;
            cmd.Parameters["@itemidlist"].TypeName = "del_itemid_list";
            cmd.Parameters["@itemidlist"].Value = itemid_list;
            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            foreach (int itemid in itemids)
            {
                DataRow[] rows = itemlist.Select("ID=" + Convert.ToString(itemid));
                foreach (DataRow row in rows)
                {
                    itemlist.Rows.Remove(row);
                }
            }
        }

        
    }
    public class KeywordItem
    {
        public Int64 KeywordID { get; set; }
        public string Keyword { get; set; }
    }
    public class ItemEdit
    {
        //receive string values from the useredititem view
        public string itemid { get; set; }
        [Required]
        [Display(Name="商品名称")]
        public string itemname { get; set; }
        [Display(Name="商品序号")]
        [DefaultSettingValue("0")]
        public string itemSN { get; set; }
        [Required]
        [Display(Name="商品图片")]
        public string image { get; set; }
        public string addtime { get; set; }
        [Required]
        [Display(Name="商品链接")]
        public string linkAddr { get; set; }
        //[Required]
        [Display(Name="关键字")]
        public string keywordlist { get; set; }
        [Display(Name = "商品价格")]
        public string price { get; set; }
        [Display(Name = "商品简介")]
        public string intro { get; set; }
        //------------------------------------------------
        //methods formatting the string value to the actual value
        public Int64 GetItemID()
        {
            return Convert.ToInt64(itemid);
        }
        public string GetItemName()
        {
            return itemname;
        }
        public Int64 GetItemSN()
        {
            return Convert.ToInt64(itemSN);
        }
        public string GetImage()
        {
            return image;
        }
        public DateTime GetAddTime()
        {
            return Convert.ToDateTime(addtime);
        }
        public string GetLinkAddr()
        {
            return linkAddr;
        }
        public decimal GetPrice()
        {
            return Convert.ToDecimal(price);
        }
        public string GetIntro()
        {
            return intro;
        }
        
        //编辑商品信息时获取商品关键字
        public KeywordItem[] GetKeywordList()
        {
            KeywordItem[] keywords;
            if (string.Equals(keywordlist,"keyword=")==true) return null;  //ueseredititem jquery ui sortable 控件
            if (string.IsNullOrEmpty(keywordlist) == true) return null;   //useradditem
            if (keywordlist.IndexOf('=') != -1)  //编辑关键字
            {
                string[] keyworditem = keywordlist.Split('&');
                keywords = new KeywordItem[keyworditem.Length];
                for (int i = 0; i < keyworditem.Length; i++)
                {
                    keywords[i] = new KeywordItem();
                    string[] temp = keyworditem[i].Split('@');
                    keywords[i].Keyword = temp[1];
                    keywords[i].KeywordID = Convert.ToInt64(temp[0].Remove(0, 8));
                }
            }
            else        //添加关键字
            {
                string[] keyworditem = keywordlist.Split(' ');
                keywords = new KeywordItem[keyworditem.Length];
                for (int i = 0; i < keyworditem.Length; i++)
                {
                    keywords[i] = new KeywordItem();
                    if (string.IsNullOrEmpty(keyworditem[i]) != true)
                    {
                        keywords[i].Keyword = keyworditem[i];
                        keywords[i].KeywordID = 0;
                    }
                    else
                    {
                        keywords[i].Keyword = null;
                        keywords[i].KeywordID = -1;
                    }
                }
            }
            return keywords;
        }
    }
    public class Admin
    {
        private IQueryable<Brand> brands;
        public IQueryable<Brand> Brands
        {
            get { return brands; }
        }
        public Admin()
        {
            var db = new EFDbContext("name=cj");
            brands = db.Brands/*.Select(b=>b)*/.OrderBy(w=>w.ID);

        }
    }
    public class UserAccount
    {
        private IQueryable<User> users;
        private EFDbContext db;
        public UserAccount()
        {
            db = new EFDbContext("name=cj");
            users = db.Users.OrderBy(u => u.ID);
        }
        //when call this constructor, only one matched user is returned
        public UserAccount(string username)
        {
            db = new EFDbContext("name=cj");
            users = db.Users.Where(u => u.username == username);
        }

        public IQueryable<User> Users
        {
            get { return users; }
        }
        public void SaveChange()
        {
            db.SaveChanges();
        }
    }

    public class ReturnUrlParam
    {
        public int orginpagenumber{get;set;}
        public string orginsortorder{get;set;}
        public string itemname_search{get;set;}
        public string itemSN_search{get;set;}
        public string addtime_search{get;set;}
        public string linkaddr_search{get;set;}
        public void SetViewBag(dynamic ViewBag)
        {
            ViewBag.orginpagenumber = orginpagenumber;
            ViewBag.orginsortorder = orginsortorder;
            ViewBag.itemname_search = itemname_search;
            ViewBag.itemSN_search = itemSN_search;
            ViewBag.addtime_search = addtime_search;
            ViewBag.linkaddr_search = linkaddr_search;
        }
        
    }
}